package cn.iocoder.yudao.module.trade.statistics.dal.mysql;

import cn.iocoder.yudao.framework.business.basic.dao.mapper.CoreMapper;
import cn.iocoder.yudao.module.trade.statistics.bo.TradeOrderAreaStatisticsRespBO;
import cn.iocoder.yudao.module.trade.statistics.bo.TradeOrderMemberRankingRespBO;
import cn.iocoder.yudao.module.trade.statistics.bo.TradeOrderProductRankingRespBO;
import cn.iocoder.yudao.module.trade.statistics.dal.dataobject.TradeStatisticsDO;
import cn.iocoder.yudao.module.trade.statistics.vo.TradeOrderSummaryRespVO;
import cn.iocoder.yudao.module.trade.statistics.vo.TradeOrderTrendRespVO;
import org.apache.ibatis.annotations.Mapper;
import org.apache.ibatis.annotations.Param;
import org.apache.ibatis.annotations.Select;

import java.time.LocalDateTime;
import java.util.List;

/**
 * 交易订单的统计 Mapper
 *
 * @author owen
 */
@Mapper
public interface TradeOrderStatisticsMapper extends CoreMapper<TradeStatisticsDO> {

    @Select("""
                    SELECT receiver_area_id AS areaId,
                        (
                            SELECT COUNT(DISTINCT s.user_id)
                            FROM trade_order AS s
                            WHERE s.receiver_area_id = m.receiver_area_id
                        ) AS orderCreateUserCount,
                        (
                            SELECT COUNT(DISTINCT s.user_id)
                            FROM trade_order AS s
                            WHERE s.receiver_area_id = m.receiver_area_id
                                AND s.pay_status = TRUE AND s.deleted = FALSE
                        ) AS orderPayUserCount,
                        (
                            SELECT IFNULL(SUM(s.pay_price), 0)
                            FROM trade_order AS s
                            WHERE s.receiver_area_id = m.receiver_area_id
                                AND s.pay_status = TRUE
                                AND s.deleted = FALSE
                        ) AS orderPayPrice
                    FROM trade_order m
                    WHERE deleted = FALSE
                    GROUP BY receiver_area_id
            """)
    List<TradeOrderAreaStatisticsRespBO> selectSummaryListGroupByAreaId();

    @Select("""
                    SELECT COUNT(1)
                    FROM trade_order
                    WHERE create_time BETWEEN #{beginTime} AND #{endTime} AND deleted = FALSE
            """)
    Integer selectCountByCreateTimeBetween(@Param("beginTime") LocalDateTime beginTime,
                                           @Param("endTime") LocalDateTime endTime);

    @Select("""
            SELECT COUNT(1)
            FROM trade_order
            WHERE pay_status = TRUE AND create_time BETWEEN #{beginTime} AND #{endTime} AND deleted = FALSE
            """)
    Integer selectCountByPayTimeBetween(@Param("beginTime") LocalDateTime beginTime,
                                        @Param("endTime") LocalDateTime endTime);

    @Select("""
            SELECT count(1)
            FROM trade_order
            WHERE deleted = FALSE
            """)
    Integer selectOrderCount();

    @Select("""
            SELECT count(1)
            FROM trade_order
            WHERE pay_status = TRUE AND deleted = FALSE
            """)
    Integer selectPayOrderCount();

    @Select("""
            SELECT IFNULL(SUM(pay_price), 0)
            FROM trade_order
            WHERE deleted = FALSE
            """)
    Integer selectSummaryPrice();

    @Select("""
            SELECT IFNULL(SUM(pay_price), 0)
            FROM trade_order
            WHERE pay_status = TRUE AND deleted = FALSE
            """)
    Integer selectSummaryPriceByPayed();

    @Select("""
            SELECT IFNULL(SUM(pay_price), 0)
            FROM trade_order AS s
            WHERE s.pay_status = TRUE AND deleted = FALSE AND create_time BETWEEN #{beginTime} AND #{endTime}
            """)
    Integer selectSummaryPriceByPayTimeBetween(@Param("beginTime") LocalDateTime beginTime,
                                               @Param("endTime") LocalDateTime endTime);

    @Select("""
            SELECT COUNT(DISTINCT (user_id))
            FROM trade_order
            WHERE deleted = FALSE AND create_time BETWEEN #{beginTime} AND #{endTime}
            """)
    Integer selectUserCountByCreateTimeBetween(@Param("beginTime") LocalDateTime beginTime,
                                               @Param("endTime") LocalDateTime endTime);

    @Select("""
            SELECT COUNT(DISTINCT (user_id))
            FROM trade_order
            WHERE pay_time BETWEEN #{beginTime} AND #{endTime} AND pay_status = TRUE AND deleted = FALSE
            """)
    Integer selectUserCountByPayTimeBetween(@Param("beginTime") LocalDateTime beginTime,
                                            @Param("endTime") LocalDateTime endTime);

    /**
     * 按照支付时间统计订单（按天分组）
     *
     * @param beginTime 支付起始时间
     * @param endTime   支付截止时间
     * @return 订单统计列表
     */
    @Select("""
            SELECT DATE_FORMAT(pay_time, '%Y-%m-%d') AS date, COUNT(1) AS orderPayCount, IFNULL(SUM(pay_price), 0) AS orderPayPrice
            FROM trade_order
            WHERE pay_status = TRUE AND create_time BETWEEN #{beginTime} AND #{endTime} AND deleted = FALSE
            GROUP BY date
            """)
    List<TradeOrderTrendRespVO> selectListByPayTimeBetweenAndGroupByDay(@Param("beginTime") LocalDateTime beginTime,
                                                                        @Param("endTime") LocalDateTime endTime);

    /**
     * 按照支付时间统计订单（按月分组）
     *
     * @param beginTime 支付起始时间
     * @param endTime   支付截止时间
     * @return 订单统计列表
     */
    @Select("""
            SELECT DATE_FORMAT(pay_time, '%Y-%m') AS date, COUNT(1) AS order_pay_count, IFNULL(SUM(pay_price), 0) AS order_pay_price
            FROM trade_order
            WHERE pay_status = TRUE AND create_time BETWEEN #{beginTime} AND #{endTime} AND deleted = FALSE
            GROUP BY date
            """)
    List<TradeOrderTrendRespVO> selectListByPayTimeBetweenAndGroupByMonth(@Param("beginTime") LocalDateTime beginTime,
                                                                          @Param("endTime") LocalDateTime endTime);

    @Select("""
            SELECT COUNT(1)
            FROM trade_order
            WHERE status = #{status} ANd delivery_type = #{deliveryType} AND deleted = FALSE
            """)
    Long selectCountByStatusAndDeliveryType(@Param("status") Integer status, @Param("deliveryType") Integer deliveryType);

    @Select("""
            SELECT IFNULL(SUM(pay_price), 0) AS order_pay_price, COUNT(1) AS order_pay_count
            FROM trade_order
            WHERE pay_status = #{payStatus} AND pay_time BETWEEN #{beginTime} AND #{endTime} AND deleted = FALSE
            """)
    TradeOrderSummaryRespVO selectPaySummaryByPayStatusAndPayTimeBetween(@Param("payStatus") Boolean payStatus,
                                                                         @Param("beginTime") LocalDateTime beginTime,
                                                                         @Param("endTime") LocalDateTime endTime);

    @Select("""
            SELECT MIN(b.spu_id) AS spu_id, MIN(b.spu_name) AS spu_name, MIN(b.pic_url) as spu_pic_url, COUNT(b.id) AS sales_count
            FROM trade_order AS a, trade_order_item AS b
            WHERE a.id = b.order_id AND a.pay_status = TRUE AND a.deleted = FALSE
            GROUP BY b.spu_id, b.spu_name
            ORDER BY sales_count DESC
            LIMIT 0,#{limit}
            """)
    List<TradeOrderProductRankingRespBO> selectOrderProductRanking(@Param("limit") Integer limit);

    @Select("""
            SELECT MIN(a.user_id) AS user_id, MIN(b.nickname) AS user_nickname, MIN(b.avatar) AS user_avatar, SUM(a.pay_price) AS consume_price
            FROM trade_order AS a, member_user AS b
            WHERE a.user_id = b.id AND a.pay_status = TRUE AND a.deleted = FALSE
            GROUP BY b.id, b.nickname, b.avatar
            ORDER BY consume_price DESC
            LIMIT 0,#{limit}
            """)
    List<TradeOrderMemberRankingRespBO> selectOrderMemberRanking(@Param("limit") Integer limit);

}
